﻿using MySql.Data.MySqlClient;
using System.Data;

namespace ZeroSumDBM.Core.MySql
{
    public static class MysqlHelper
    {
        private static string GenerateConnection(string host, string account, string pwd, string port)
        {
            return $"server={host};user id={account};password={pwd};port={port};sslmode=None";
        }

        private static string GenerateConnection(string host, string account, string pwd, string port, string database)
        {
            return $"server={host};user id={account};password={pwd};port={port};database={database};sslmode=None";
        }

        public static bool TestConnection(string host, string account, string pwd, string port)
        {
            try
            {
                var conf = GenerateConnection(host, account, pwd, port);
                using var conn = new MySqlConnection(conf);
                conn.Open();
                return true;
            }
#pragma warning disable CS0168 // 声明了变量“e”，但从未使用过
            catch (Exception e)
#pragma warning restore CS0168 // 声明了变量“e”，但从未使用过
            {
                return false;
            }
        }

        public static IEnumerable<string> ShowDataBase(string host, string account, string pwd, string port)
        {
            var conf = GenerateConnection(host, account, pwd, port);
            var dataBases = new List<string>();
            using var conn = new MySqlConnection(conf);
            conn.Open();
            var comm = new MySqlDataAdapter("SHOW DATABASES", conn);
            var ds = new DataSet();
            comm.Fill(ds);
            var dt = ds.Tables[0];
            for (var i = 0; i < dt.Rows.Count; i++)
            {
#pragma warning disable CS8604 // “void List<string>.Add(string item)”中的形参“item”可能传入 null 引用实参。
                dataBases.Add(dt.Rows[i].ItemArray.FirstOrDefault()?.ToString());
#pragma warning restore CS8604 // “void List<string>.Add(string item)”中的形参“item”可能传入 null 引用实参。
            }

            return dataBases;
        }

        public static List<string> ShowTables(string host, string account, string password, string port, string database)
        {
            var conf = GenerateConnection(host, account, password, port, database);
            var tables = new List<string>();

            using var conn = new MySqlConnection(conf);
            conn.Open();
            var comm = new MySqlDataAdapter("SHOW TABLES", conn);
            var ds = new DataSet();
            comm.Fill(ds);
            var dt = ds.Tables[0];
            for (int i = 0; i < dt.Rows.Count; i++)
            {
#pragma warning disable CS8604 // “void List<string>.Add(string item)”中的形参“item”可能传入 null 引用实参。
                tables.Add(dt.Rows[i].ItemArray.FirstOrDefault()?.ToString());
#pragma warning restore CS8604 // “void List<string>.Add(string item)”中的形参“item”可能传入 null 引用实参。
            }

            return tables;
        }

        public static SearchResult GetTableData(string host, string account, string password, string port,
            string database, string table, int page, int pageSize)
        {
            var conf = GenerateConnection(host, account, password, port, database);
            using var conn = new MySqlConnection(conf);
            conn.Open();

            var offset = (page - 1) * pageSize + 1;
            var comm = new MySqlDataAdapter($"SELECT * FROM `{table}` LIMIT {offset}, {pageSize}", conn);
            var ds = new DataSet();
            comm.Fill(ds);


            var dt = ds.Tables[0];
            var result = new SearchResult();

            for (int i = 0; i < dt.Columns.Count; i++)
            {
                var column = dt.Columns[i].ColumnName;
                result.Columns.Add(column);
            }

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                var row = dt.Rows[i];
                var item = new Dictionary<string, object?>();

                foreach (DataColumn column in dt.Columns)
                {
                    var name = column.ColumnName;
                    var value = Convert.IsDBNull(row[name]) ? null : row[name];
                    item.Add(name, value);
                }

                result.Rows.Add(item);
            }


            var cmd = new MySqlCommand($"SELECT COUNT(*) FROM `{table}`", conn);
            var reader = cmd.ExecuteScalar();

            result.Count = reader != null ? Int32.Parse(reader.ToString() ?? string.Empty) : 0;
            result.Pages = result.Count / pageSize + (result.Count % pageSize > 0 ? 1 : 0);

            return result;
        }
    }

    public class SearchResult
    {
        public IList<string> Columns { get; set; } = new List<string>();
        public IList<Dictionary<string, object?>> Rows { get; set; } = new List<Dictionary<string, object?>>();
        public int Count { get; set; }
        public int Pages { get; set; }
    }
}
